package com.jadcon.bedbreakfast.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Component("reservationRepository")
public class ReservationRepositoryImpl implements ReservationRepository {

	private SimpleJdbcTemplate simpleJdbcTemplate;

	@SuppressWarnings("unchecked")
	public List<Reservation> getReservationList() {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper());
	}
	
	@SuppressWarnings("unchecked")
	public List<Reservation> getReservationList(String username) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where username=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(), username);
	}

	public void save(Reservation reservation) {
		if (reservation.getReservationNumber() == 0) {
			insert(reservation);
		} else {
			update(reservation);
		}
	}

	private void insert(Reservation reservation) {

		String sql = "INSERT INTO reservation (roomNumber, totalNumberOfGuests, checkIn, checkOut, username) VALUES (?, ?, ?, ?, ?)";
		simpleJdbcTemplate.update(sql, reservation.getRoomNumber(), reservation
				.getTotalNumberOfGuests(), reservation.getCheckIn(),
				reservation.getCheckOut(), reservation.getUsername());
	}

	private void update(Reservation reservation) {

		String sql = "UPDATE reservation SET roomNumber=?, totalNumberOfGuests=?, checkIn=?, checkOut=?, username=? WHERE id=?";
		simpleJdbcTemplate.update(sql, reservation.getRoomNumber(), reservation
				.getTotalNumberOfGuests(), reservation.getCheckIn(),
				reservation.getCheckOut(), reservation.getUsername());
	}

	@SuppressWarnings("unchecked")
	public List<Reservation> findByNumber(int reservationNumber) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where id=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				reservationNumber);
	}

	@SuppressWarnings("unchecked")
	public List<Reservation> findByNumber(int reservationNumber, String username) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where id=? and username=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				reservationNumber, username);
	}

	@SuppressWarnings("unchecked")
	public List<Reservation> findReservationByRoom(int roomNumber) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where roomNumber=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				roomNumber);
	}
	
	@SuppressWarnings("unchecked")
	public List<Reservation> findReservationByRoom(int roomNumber, String username) {
		
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where roomNumber=? and username=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				roomNumber, username);
	}

	@SuppressWarnings("unchecked")
	public List<Reservation> findReservationByStartDate(Date checkIn) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where checkIn=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				checkIn);
	}
	
	@SuppressWarnings("unchecked")
	public List<Reservation> findReservationByStartDate(Date checkIn, String username) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation where checkIn=? and username=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(),
				checkIn, username);
	}

	public void delete(int reservationNumber) {
		String sql = "delete from reservation where id=?";
		simpleJdbcTemplate.update(sql, reservationNumber);
	}

	public Reservation getReservationNumberForNewlyCreatedReservation(
			Reservation newReservation) {
		Date checkIn = newReservation.getCheckIn();
		Date checkOut = newReservation.getCheckOut();
		int totalNumberOfGuests = newReservation.getTotalNumberOfGuests();
		int roomNumber = newReservation.getRoomNumber();
		String username = newReservation.getUsername();

		List<Reservation> reservationList = findReservationByStartDate(checkIn);
		for (Reservation reservation : reservationList) {
			if (reservation.getCheckOut().equals(checkOut)
					&& reservation.getTotalNumberOfGuests() == (totalNumberOfGuests)
					&& reservation.getRoomNumber() == roomNumber
					&& reservation.getUsername().equals(username)) {
				return reservation;
			}
		}
		return newReservation;
	}
	
	@SuppressWarnings("unchecked")
	public List<Reservation> findByUsername(String username) {
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, username FROM reservation WHERE username=?";
		return this.simpleJdbcTemplate.query(sql, new ReservationMapper(), username);		

	}

	@SuppressWarnings("unchecked")
	private static final class ReservationMapper implements RowMapper {

		public Reservation mapRow(ResultSet rs, int rowNum) throws SQLException {
			int reservationNumber = rs.getInt("id");
			int roomNumber = rs.getInt("roomNumber");
			int totalNumberOfGuests = rs.getInt("totalNumberOfGuests");
			Date checkIn = rs.getDate("checkIn");
			Date checkOut = rs.getDate("checkOut");
			String username = rs.getString("username");

			Reservation reservation = new Reservation(reservationNumber,
					roomNumber, totalNumberOfGuests, checkIn, checkOut, username);
			return reservation;
		}
	}

	@Autowired
	public void setDataSource(DataSource dataSource) {
		simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

}
